/*
 * Created on May 25, 2004
 * by Mihai Postelnicu
 * email mihai@code.ro
 *
 *

 */
package ro.gateway.aida.search;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.utils.LocalisedList;

;
/**
 * @author Mihai Postelnicu email mihai@code.ro
 */
public class SearchColumnDB extends DBPersistenceManager {
	private SearchColumnDB(PersistenceToken token) {
		super(token);
	}

	public static SearchColumnDB getManager(PersistenceToken token) {
		return new SearchColumnDB(token);
	}

	protected List getFundingOrgColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_FUNDING_ORG_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			LangStringCell lsc = null;
			long lid = -1;
			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(ResultsFormat.FUNDING_ORG_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));				
				}
				if (rid != rs.getLong(1) || lid != rs.getInt(2)) {
					lsc = new LangStringCell(ResultsFormat.FUNDING_ORG_COLUMN);
					rlc.getItems().add(lsc);
					lsc.setProjectId(rs.getLong(1));
				}
				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				lid = rs.getInt(2);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getExecutingOrgColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_EXECUTING_ORG_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			LangStringCell lsc = null;
			long lid = -1;
			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(ResultsFormat.EXECUTING_ORG_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}
				if (rid != rs.getLong(1) || lid != rs.getInt(2)) {
					lsc = new LangStringCell(ResultsFormat.EXECUTING_ORG_COLUMN);
					rlc.getItems().add(lsc);
					lsc.setProjectId(rs.getLong(1));
				}
				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				lid = rs.getInt(2);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getImplementingOrgColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_IMPLEMENTING_ORG_COLUMN + idquery
							+ MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			LangStringCell lsc = null;
			long lid = -1;
			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(
							ResultsFormat.IMPLEMENTING_ORG_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}
				if (rid != rs.getLong(1) || lid != rs.getInt(2)) {
					lsc = new LangStringCell(
							ResultsFormat.IMPLEMENTING_ORG_COLUMN);
					rlc.getItems().add(lsc);
					lsc.setProjectId(rs.getLong(1));
				}
				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				lid = rs.getInt(2);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getFundingStatusColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_FUNDING_STATUS_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			LangStringCell lsc = null;
			long lid = -1;
			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(
							ResultsFormat.FUNDING_STATUS_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}
				if (rid != rs.getLong(1) || lid != rs.getInt(2)) {
					lsc = new LangStringCell(
							ResultsFormat.FUNDING_STATUS_COLUMN);
					rlc.getItems().add(lsc);
					lsc.setProjectId(rs.getLong(1));
				}
				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				lid = rs.getInt(2);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getTermsAssistanceColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_TERMS_ASSISTANCE_COLUMN + idquery
							+ MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			LangStringCell lsc = null;
			long lid = -1;
			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(
							ResultsFormat.TERMS_ASSISTANCE_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}
				if (rid != rs.getLong(1) || lid != rs.getInt(2)) {
					lsc = new LangStringCell(
							ResultsFormat.TERMS_ASSISTANCE_COLUMN);
					rlc.getItems().add(lsc);
					lsc.setProjectId(rs.getLong(1));
				}
				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				lid = rs.getInt(2);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getTypesAssistanceColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_TYPES_ASSISTANCE_COLUMN + idquery
							+ MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			LangStringCell lsc = null;
			long lid = -1;
			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(
							ResultsFormat.TYPES_ASSISTANCE_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}
				if (rid != rs.getLong(1) || lid != rs.getInt(2)) {
					lsc = new LangStringCell(
							ResultsFormat.TYPES_ASSISTANCE_COLUMN);
					rlc.getItems().add(lsc);
					lsc.setProjectId(rs.getLong(1));
				}
				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				lid = rs.getInt(2);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getFundingAmountColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_FUNDING_AMOUNT_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;

			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(
							ResultsFormat.FUNDING_AMOUNT_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}

				CurrencyCell lsc = new CurrencyCell(
						ResultsFormat.FUNDING_AMOUNT_COLUMN);
				lsc.setItem(rs.getLong(2));
				lsc.setSymbol(rs.getString(3));
				rlc.getItems().add(lsc);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getFundingAmountOrgColumn(String idquery)
			throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_FUNDING_AMOUNT_ORG_COLUMN + idquery
							+ MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			TupleCell c = null;
			LangStringCell lsc = null;
			long rid = -1;
			long lid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}
				if (rid != rs.getLong(1) || lid != rs.getLong(2) ) {
					c = new TupleCell(ResultsFormat.FUNDING_AMOUNT_ORG_COLUMN);
					rlc.getItems().add(c);
					c.setProjectId(rs.getLong(1));
					CurrencyCell cc = new CurrencyCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_COLUMN);
					cc.setItem(rs.getLong(5));
					cc.setSymbol(rs.getString(6));
					cc.setProjectId(rs.getLong(1));

					lsc = new LangStringCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_COLUMN);
					lsc.setProjectId(rs.getLong(1));

					c.getItems().add(cc);
					c.getItems().add(lsc);

				}

				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				rid = rs.getLong(1);
				lid = rs.getLong(2);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getFundingAmountOrgDisbColumn(String idquery)
			throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_FUNDING_AMOUNT_ORG_DISB_COLUMN
							+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			TupleCell c = null;
			LangStringCell lsc = null;
			StringCell dc= null;
			long rid = -1;
			long lid = -1;
			long year = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_DISB_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}
				if (lid != rs.getLong(2) || lsc.getProjectId() != rs.getLong(1) || year!=rs.getInt(7) ) {
					c = new TupleCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_DISB_COLUMN);
					rlc.getItems().add(c);
					c.setProjectId(rs.getLong(1));
					CurrencyCell cc = new CurrencyCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_DISB_COLUMN);
					cc.setItem(rs.getLong(5));
					cc.setSymbol(rs.getString(6));
					cc.setProjectId(rs.getLong(1));

					dc=new StringCell(ResultsFormat.FUNDING_AMOUNT_ORG_DISB_COLUMN);
					dc.setProjectId(rs.getLong(1));
					dc.setItem(Integer.toString(rs.getInt(7)));
					year=rs.getInt(7);
					
					
					
					lsc = new LangStringCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_DISB_COLUMN);
					lsc.setProjectId(rs.getLong(1));

					c.getItems().add(cc);
					c.getItems().add(dc);
					c.getItems().add(lsc);

				}

				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				rid = rs.getLong(1);
				lid = rs.getLong(2);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getFundingAmountOrgCommColumn(String idquery)
			throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_FUNDING_AMOUNT_ORG_COMM_COLUMN
							+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			TupleCell c = null;
			LangStringCell lsc = null;
			long rid = -1;
			long lid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_COMM_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}
				if (rid != rs.getLong(1) || lid != rs.getLong(2) ) {
					c = new TupleCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_COMM_COLUMN);
					rlc.getItems().add(c);
					c.setProjectId(rs.getLong(1));
					CurrencyCell cc = new CurrencyCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_COMM_COLUMN);
					cc.setItem(rs.getLong(5));
					cc.setSymbol(rs.getString(6));
					cc.setProjectId(rs.getLong(1));

					lsc = new LangStringCell(
							ResultsFormat.FUNDING_AMOUNT_ORG_COMM_COLUMN);
					lsc.setProjectId(rs.getLong(1));

					c.getItems().add(cc);
					c.getItems().add(lsc);

				}

				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				rid = rs.getLong(1);
				lid = rs.getLong(2);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getSectorColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_SECTOR_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;
			LangStringCell lsc = null;
			long lid = -1;
			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(ResultsFormat.SECTOR_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}
				if (rid != rs.getLong(1) || lid != rs.getInt(2)) {
					lsc = new LangStringCell(ResultsFormat.SECTOR_COLUMN);
					rlc.getItems().add(lsc);
					lsc.setProjectId(rs.getLong(1));
				}
				lsc.getItem().setString(rs.getString(4), rs.getString(3));
				lid = rs.getInt(2);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getCountyColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_COUNTY_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;

			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(ResultsFormat.COUNTY_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}

				StringCell lsc = new StringCell(ResultsFormat.COUNTY_COLUMN);
				lsc.setItem(rs.getString(3));
				rlc.getItems().add(lsc);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getTownColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_TOWN_COLUMN + idquery
					+ MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;

			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(ResultsFormat.TOWN_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}

				StringCell lsc = new StringCell(ResultsFormat.TOWN_COLUMN);
				lsc.setItem(rs.getString(3));
				rlc.getItems().add(lsc);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getRegionColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_REGION_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;

			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(ResultsFormat.REGION_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}

				StringCell lsc = new StringCell(ResultsFormat.REGION_COLUMN);
				lsc.setItem(rs.getString(3));
				rlc.getItems().add(lsc);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getCRegionColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_CREGION_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;

			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(ResultsFormat.CREGION_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}

				StringCell lsc = new StringCell(ResultsFormat.CREGION_COLUMN);
				lsc.setItem(rs.getString(3));
				rlc.getItems().add(lsc);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getCountryColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_COUNTRY_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = null;

			long rid = -1;
			while (rs.next()) {
				if (rid != rs.getLong(1)) {
					rlc = new ResultListCell(ResultsFormat.COUNTRY_COLUMN);
					ret.add(rlc);
					rlc.setProjectId(rs.getLong(1));
				}

				StringCell lsc = new StringCell(ResultsFormat.COUNTRY_COLUMN);
				lsc.setItem(rs.getString(3));
				rlc.getItems().add(lsc);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getProjectIdColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_PROJECT_ID_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			ResultListCell rlc = new ResultListCell(
					ResultsFormat.PROJECT_ID_COLUMN);
			long rid = 0;
			while (rs.next()) {
				if (rid != rs.getInt(1)) {
					rlc = new ResultListCell(ResultsFormat.PROJECT_ID_COLUMN);
					rlc.setProjectId(rs.getLong(1));
					ret.add(rlc);
				}
				StringCell sc = new StringCell(ResultsFormat.PROJECT_ID_COLUMN);
				sc.setProjectId(rs.getLong(1));
				sc.setItem(rs.getString(2));
				rlc.getItems().add(sc);
				rid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getStatusColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_STATUS_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			LangStringCell lsc = new LangStringCell(ResultsFormat.STATUS_COLUMN);
			long lid = 0;
			while (rs.next()) {
				if (lid != rs.getLong(1)) {
					lsc = new LangStringCell(ResultsFormat.STATUS_COLUMN);
					lsc.setProjectId(rs.getLong(1));
					ret.add(lsc);
				}
				lsc.getItem().setString(rs.getString(3), rs.getString(2));
				lid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getTitleColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_TITLE_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			LangStringCell lsc = new LangStringCell(ResultsFormat.TITLE_COLUMN);
			long lid = 0;
			while (rs.next()) {
				if (lid != rs.getLong(1)) {
					lsc = new LangStringCell(ResultsFormat.TITLE_COLUMN);
					lsc.setProjectId(rs.getLong(1));
					ret.add(lsc);
				}
				lsc.getItem().setString(rs.getString(3), rs.getString(2));
				lid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getDescriptionColumn(String idquery) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(Q_DESCRIPTION_COLUMN
					+ idquery + MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			LangStringCell lsc = new LangStringCell(
					ResultsFormat.DESCRIPTION_COLUMN);
			long lid = 0;
			while (rs.next()) {
				if (lid != rs.getLong(1)) {
					lsc = new LangStringCell(ResultsFormat.DESCRIPTION_COLUMN);
					lsc.setProjectId(rs.getLong(1));
					ret.add(lsc);
				}
				lsc.getItem().setString(rs.getString(3), rs.getString(2));
				lid = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	protected List getProposedApprovalDateColumn(String idquery)
			throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_PROPOSED_APPROVAL_DATE_COLUMN + idquery
							+ MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			StringCell lsc;

			while (rs.next()) {
				lsc = new StringCell(
						ResultsFormat.PROPOSED_APPROVAL_DATE_COLUMN);
				lsc.setProjectId(rs.getLong(1));
				ret.add(lsc);
				lsc.setItem(rs.getString(2));
			}
			rs.close();
			ps.close();

		} finally {
			con.close();
		}
		return ret;
	}

	protected List getActualApprovalDateColumn(String idquery)
			throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_ACTUAL_APPROVAL_DATE_COLUMN + idquery
							+ MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			StringCell lsc;

			while (rs.next()) {
				lsc = new StringCell(ResultsFormat.ACTUAL_APPROVAL_DATE_COLUMN);
				lsc.setProjectId(rs.getLong(1));
				ret.add(lsc);
				lsc.setItem(rs.getString(2));
			}
			rs.close();
			ps.close();

		} finally {
			con.close();
		}
		return ret;
	}

	protected List getProposedStartingDateColumn(String idquery)
			throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_PROPOSED_STARTING_DATE_COLUMN + idquery
							+ MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			StringCell lsc;
			while (rs.next()) {
				lsc = new StringCell(
						ResultsFormat.PROPOSED_STARTING_DATE_COLUMN);
				lsc.setProjectId(rs.getLong(1));
				ret.add(lsc);
				lsc.setItem(rs.getString(2));
			}
			rs.close();
			ps.close();

		} finally {
			con.close();
		}
		return ret;
	}

	protected List getActualStartingDateColumn(String idquery)
			throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();
		try {
			PreparedStatement ps = con
					.prepareStatement(Q_ACTUAL_STARTING_DATE_COLUMN + idquery
							+ MASTER_ORDER);
			ResultSet rs = ps.executeQuery();
			StringCell lsc;

			while (rs.next()) {
				lsc = new StringCell(ResultsFormat.ACTUAL_STARTING_DATE_COLUMN);
				lsc.setProjectId(rs.getLong(1));
				ret.add(lsc);
				lsc.setItem(rs.getString(2));
			}
			rs.close();
			ps.close();
		} finally {
			con.close();
		}
		return ret;
	}

	public List getResultsColumn(String ctype, String idquery)
			throws SQLException {
		if (ctype.equals(ResultsFormat.TITLE_COLUMN))
			return getTitleColumn(idquery);
		if (ctype.equals(ResultsFormat.COUNTY_COLUMN))
			return getCountyColumn(idquery);
		if (ctype.equals(ResultsFormat.COUNTRY_COLUMN))
			return getCountryColumn(idquery);
		if (ctype.equals(ResultsFormat.REGION_COLUMN))
			return getRegionColumn(idquery);
		if (ctype.equals(ResultsFormat.CREGION_COLUMN))
			return getCRegionColumn(idquery);
		if (ctype.equals(ResultsFormat.TOWN_COLUMN))
			return getTownColumn(idquery);
		if (ctype.equals(ResultsFormat.SECTOR_COLUMN))
			return getSectorColumn(idquery);
		if (ctype.equals(ResultsFormat.FUNDING_ORG_COLUMN))
			return getFundingOrgColumn(idquery);
		if (ctype.equals(ResultsFormat.IMPLEMENTING_ORG_COLUMN))
			return getImplementingOrgColumn(idquery);
		if (ctype.equals(ResultsFormat.EXECUTING_ORG_COLUMN))
			return getExecutingOrgColumn(idquery);
		if (ctype.equals(ResultsFormat.FUNDING_AMOUNT_COLUMN))
			return getFundingAmountColumn(idquery);
		if (ctype.equals(ResultsFormat.FUNDING_AMOUNT_ORG_COLUMN))
			return getFundingAmountOrgColumn(idquery);
		if (ctype.equals(ResultsFormat.FUNDING_AMOUNT_ORG_DISB_COLUMN))
			return getFundingAmountOrgDisbColumn(idquery);
		if (ctype.equals(ResultsFormat.FUNDING_AMOUNT_ORG_COMM_COLUMN))
			return getFundingAmountOrgCommColumn(idquery);
		if (ctype.equals(ResultsFormat.FUNDING_STATUS_COLUMN))
			return getFundingStatusColumn(idquery);
		if (ctype.equals(ResultsFormat.PROPOSED_APPROVAL_DATE_COLUMN))
			return this.getProposedApprovalDateColumn(idquery);
		if (ctype.equals(ResultsFormat.PROPOSED_STARTING_DATE_COLUMN))
			return this.getProposedStartingDateColumn(idquery);
		if (ctype.equals(ResultsFormat.ACTUAL_STARTING_DATE_COLUMN))
			return this.getActualStartingDateColumn(idquery);
		if (ctype.equals(ResultsFormat.ACTUAL_APPROVAL_DATE_COLUMN))
			return this.getActualApprovalDateColumn(idquery);
		if (ctype.equals(ResultsFormat.STATUS_COLUMN))
			return this.getStatusColumn(idquery);
		if (ctype.equals(ResultsFormat.PROJECT_ID_COLUMN))
			return this.getProjectIdColumn(idquery);
		if (ctype.equals(ResultsFormat.TERMS_ASSISTANCE_COLUMN))
			return this.getTermsAssistanceColumn(idquery);
		if (ctype.equals(ResultsFormat.TYPES_ASSISTANCE_COLUMN))
			return this.getTypesAssistanceColumn(idquery);
		if (ctype.equals(ResultsFormat.DESCRIPTION_COLUMN))
			return this.getDescriptionColumn(idquery);
		return null;
	}

	public void countMaxColumnItems(List column) {
		if(column.size()==0) return;
		ResultCell rc=(ResultCell)column.get(0);
		if(ResultsFormat.EXPANDABLE.indexOf(rc.getType())!=-1) {
			Iterator i=column.iterator();
			int maxColumnItems=0;
			while (i.hasNext()) {
				ResultListCell rlc = (ResultListCell) i.next();
				if(maxColumnItems<rlc.getItems().size()) maxColumnItems=rlc.getItems().size();
			}
			i=column.iterator();
			while (i.hasNext()) {
				ResultListCell rlc = (ResultListCell) i.next();
				rlc.setXlsExpand(true);
				rlc.setMaxColumnItems(maxColumnItems);
			}
		}
	}
	
	public List createSearchResults(List column_names, String idquery)
			throws SQLException {
		ArrayList cresults = new ArrayList();
		Iterator i = column_names.iterator();
		int len = 0;
		while (i.hasNext()) {
			String c = (String) i.next();
			List column = getResultsColumn(c, idquery);
			countMaxColumnItems(column);
			len = column.size();
			cresults.add(column);

		}
		LocalisedList rresults = new LocalisedList();
		for (int k = 0; k < len; k++) {
			ResultRow rr = new ResultRow();
			rresults.add(rr);
			i = cresults.iterator();
			while (i.hasNext()) {
				List clist = (List) i.next();
				rr.add(clist.get(k));
			}
		}
		Collections.sort(rresults);
		return rresults;
	}

	public static final String MASTER_ORDER=" ) ORDER BY a.id";
	public static final String Q_STATUS_COLUMN = "SELECT a.id, ast.status, ast.lang FROM activities a "
			+ "LEFT OUTER JOIN act_status ast ON (ast.st_code=a.harm_status) WHERE a.id IN ( ";

	public static final String Q_PROJECT_ID_COLUMN = "SELECT a.id, d.donor_id  FROM activities a "
			+ "LEFT OUTER JOIN donor_ids d ON (d.activity_id=a.id) WHERE a.id IN ( ";

	public static final String Q_PROPOSED_APPROVAL_DATE_COLUMN = "SELECT a.id, a.date_pa FROM activities a WHERE a.id IN ( ";

	public static final String Q_ACTUAL_APPROVAL_DATE_COLUMN = "SELECT a.id, a.date_aa FROM activities a WHERE a.id IN ( ";

	public static final String Q_PROPOSED_STARTING_DATE_COLUMN = "SELECT a.id, a.date_ps FROM activities a WHERE a.id IN ( ";

	public static final String Q_ACTUAL_STARTING_DATE_COLUMN = "SELECT a.id, a.date_as FROM activities a WHERE a.id IN ( ";

	public static final String Q_SECTOR_COLUMN = "SELECT a.id, o.ro_aida_code, o.name, o.lang FROM activities a  "
			+ "LEFT OUTER JOIN obj_sectors o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_FUNDING_ORG_COLUMN = "SELECT a.id, o.id, o.name, o.lang FROM activities a  "
			+ "LEFT OUTER JOIN obj_funding_orgs o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_IMPLEMENTING_ORG_COLUMN = "SELECT a.id, o.id, o.name, o.lang FROM activities a  "
			+ "LEFT OUTER JOIN obj_implementing_orgs o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_EXECUTING_ORG_COLUMN = "SELECT a.id, o.id, o.name, o.lang FROM activities a  "
			+ "LEFT OUTER JOIN obj_executing_orgs o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_FUNDING_AMOUNT_COLUMN = "SELECT a.id, o.amount, o.harm_currency FROM activities a  "
			+ "LEFT OUTER JOIN obj_funding_amount o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_FUNDING_AMOUNT_ORG_COLUMN = "SELECT a.id, o.org_id, o.name, o.lang, o.amount, o.harm_currency FROM activities a  "
			+ "LEFT OUTER JOIN obj_funding_amount_org o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_FUNDING_AMOUNT_ORG_DISB_COLUMN = "SELECT a.id, o.org_id, o.name, o.lang, o.amount, o.currency,o.year FROM activities a  "
			+ "LEFT OUTER JOIN obj_funding_amount_org_disb o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_FUNDING_AMOUNT_ORG_COMM_COLUMN = "SELECT a.id, o.org_id, o.name, o.lang, o.amount, o.currency FROM activities a  "
			+ "LEFT OUTER JOIN obj_funding_amount_org_comm o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_FUNDING_STATUS_COLUMN = "SELECT a.id, o.id, o.name, o.lang FROM activities a  "
			+ "LEFT OUTER JOIN obj_funding_status o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_TERMS_ASSISTANCE_COLUMN = "SELECT a.id, o.id, o.value, o.lang FROM activities a  "
			+ "LEFT OUTER JOIN obj_terms_assist o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_TYPES_ASSISTANCE_COLUMN = "SELECT a.id, o.id, o.value, o.lang FROM activities a  "
			+ "LEFT OUTER JOIN obj_types_assist o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_COUNTRY_COLUMN = "SELECT a.id, o.country_iso3, o.name FROM activities a  "
			+ "LEFT OUTER JOIN obj_countries o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_REGION_COLUMN = "SELECT a.id, o.reg_id, o.name FROM activities a  "
			+ "LEFT OUTER JOIN obj_regions o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_COUNTY_COLUMN = "SELECT a.id, o.jud_id, o.name FROM activities a  "
			+ "LEFT OUTER JOIN obj_counties o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_TOWN_COLUMN = "SELECT a.id, o.sirsup_id, o.name FROM activities a  "
			+ "LEFT OUTER JOIN obj_towns o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_CREGION_COLUMN = "SELECT a.id, o.id, o.name FROM activities a  "
			+ "LEFT OUTER JOIN obj_cregions o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_TITLE_COLUMN = "SELECT a.id, o.name, o.lang FROM activities a  "
			+ "LEFT OUTER JOIN obj_titles o ON (a.id=o.activity_id) WHERE a.id IN ( ";

	public static final String Q_DESCRIPTION_COLUMN = "SELECT a.id, o.name, o.lang FROM activities a  "
			+ "LEFT OUTER JOIN obj_description o ON (a.id=o.activity_id) WHERE a.id IN ( ";

}